package com.tdcy.framework.dao;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import org.apache.commons.beanutils.PropertyUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;

import com.tdcy.framework.annotation.Column;
import com.tdcy.framework.annotation.Id;
import com.tdcy.framework.annotation.Table;
import com.tdcy.framework.bean.PageInfo;
import com.tdcy.framework.dao.metadata.MetaDataMapping;
import com.tdcy.framework.dao.metadata.TableMetadata;
import com.tdcy.framework.dao.orm.ArrayRowMapper;
import com.tdcy.framework.dao.orm.CommonRowMapper;
import com.tdcy.framework.dao.orm.MapRowMapper;
import com.tdcy.framework.exception.BaseException;
import com.tdcy.framework.util.StringUtils;

@SuppressWarnings("unchecked")
public class AppDaoImpl implements IDao {

	private  Logger logger = null;

	public AppDaoImpl(){
	  logger = LoggerFactory.getLogger(this.getClass());
	}
	
	
	@Resource
	private JdbcTemplate jdbcTemplate;

	@Resource
	private DefaultLobHandler defaultLobHandler;
	

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public DefaultLobHandler getDefaultLobHandler() {
		return defaultLobHandler;
	}

	public String getJdbcSchema() {
		try {
			return getJdbcTemplate().getDataSource().getConnection().getCatalog();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "";
	}

	public List<Object[]> execSqlQueryToArrays(String sql) {
		long reqTime = System.currentTimeMillis();
		List list = this.getJdbcTemplate().query(sql, new ArrayRowMapper());
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
		return list;
	}

	public List<Object[]> execSqlQueryToArrays(String sql, Object... params) {
		long reqTime = System.currentTimeMillis();
		List list = this.getJdbcTemplate().query(sql, params, new ArrayRowMapper());
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, params));
		return list;
	}

	public List<Map> execSqlQueryToMap(String sql) {
		long reqTime = System.currentTimeMillis();
		List list = this.getJdbcTemplate().query(sql, new MapRowMapper());
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
		return list;
	}

	public List<Map> execSqlQueryToMap(String sql, Object... params) {
		long reqTime = System.currentTimeMillis();
		List list = this.getJdbcTemplate().query(sql, params, new MapRowMapper());
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, params));
		return list;
	}

	public <T> T execSqlQueryOne(Class clazz, String sql) {
		List<T> list = execSqlQuery(clazz, sql);
		if (list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
	}

	public <T> T execSqlQueryOne(Class clazz, String sql, Object... args) {
		List<T> list = execSqlQuery(clazz, sql, args);
		if (list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
	}
	

	public Map execSqlQueryOneToMap( String sql) {
		List<Map> list = execSqlQueryToMap(sql);
		if (list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
	}
	
	public Map execSqlQueryOneToMap( String sql, Object... args) {
		List<Map> list = execSqlQueryToMap(sql, args);
		if (list != null && list.size() > 0)
			return list.get(0);
		else
			return null;
	}

	public <T> List<T> execSqlQuery(Class clazz, String sql) {
		long reqTime = System.currentTimeMillis();
		List<T> list = this.getJdbcTemplate().query(sql, new CommonRowMapper(clazz));
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
		return list;
	}

	public <T> List<T> execSqlQuery(Class clazz, String sql, Object... params) {
		long reqTime = System.currentTimeMillis();
		List<T> list = this.getJdbcTemplate().query(sql, params, new CommonRowMapper(clazz));
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, params));
		return list;
	}

	public boolean delete(Object object) throws BaseException {
		long reqTime = System.currentTimeMillis();
		boolean isSuccess = false;
		Class cls = object.getClass();
		Table annotation = (Table) cls.getAnnotation(Table.class);
		String tableName = annotation.name();
		String sql = "delete t from " + tableName + " as t where 1=1 ";
		List args = new ArrayList();
		Field[] fields = cls.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			boolean hasAnnotation = field.isAnnotationPresent(Id.class);
			if (hasAnnotation) {
				Column column = (Column) field.getAnnotation(Column.class);
				if (column.isdbcol()) {
					sql += " and t." + column.name() + "=? ";
				}
				try {
					args.add(PropertyUtils.getProperty(object, field.getName()));
				} catch (Exception e) {
					throw new BaseException(e.getMessage(), e);
				}
			}
		}

		this.getJdbcTemplate().update(sql, args.toArray());

		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, args));
		isSuccess = true;
		return isSuccess;
	}

	public boolean deleteByPrimaryKeyabsolute(Class cls, Object... primaryKeyValue) {
		long reqTime = System.currentTimeMillis();
		boolean isSuccess = false;
		Table annotation = (Table) cls.getAnnotation(Table.class);
		String tableName = annotation.name();
		String sql = "delete t from " + tableName + " as t where 1=1 ";
		Field[] fields = cls.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			boolean hasAnnotation = field.isAnnotationPresent(Id.class);
			if (hasAnnotation) {
				Column column = (Column) field.getAnnotation(Column.class);
				if (column.isdbcol()) {
					sql += " and t." + column.name() + "=? ";
				}
			}
		}
		int num = this.getJdbcTemplate().update(sql, primaryKeyValue);
		if (num > 0) {
			isSuccess = true;
		}

		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, primaryKeyValue));
		return isSuccess;
	}

	public boolean deleteByPrimaryKey(Class cls, Object... primaryKeyValue) {
		long reqTime = System.currentTimeMillis();
		boolean isSuccess = false;
		Table annotation = (Table) cls.getAnnotation(Table.class);
		String tableName = annotation.name();
		String sql = "delete t from  " + tableName + " as t where 1=1 ";
		Field[] fields = cls.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			boolean hasAnnotation = field.isAnnotationPresent(Id.class);
			if (hasAnnotation) {
				Column column = (Column) field.getAnnotation(Column.class);
				if (column.isdbcol()) {
					sql += " and t." + column.name() + "=? ";

				}
			}
		}
		int num = this.getJdbcTemplate().update(sql, primaryKeyValue);
		if (num > 0) {
			isSuccess = true;
		}

		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, primaryKeyValue));
		return isSuccess;
	}

	public <T> T save(T object) throws BaseException {
		Class cls = object.getClass();
		Table annotation = (Table) cls.getAnnotation(Table.class);
		String tableName = annotation.name();
		String sql = "insert into " + tableName + "(";
		String valueStr = "";
		List args = new ArrayList();
		Field[] fields = cls.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			boolean hasAnnotation = field.isAnnotationPresent(Column.class);
			if (hasAnnotation) {
				Column column = (Column) field.getAnnotation(Column.class);
				if (column.isdbcol()) {
					try {
						Object obj = PropertyUtils.getProperty(object, field.getName());
						if (obj != null) {
							sql += column.name() + ",";
							valueStr += "?,";

							if (obj instanceof Date)
								obj = new Timestamp(((Date) obj).getTime());
							args.add(obj);
						}
					} catch (Exception e) {
						throw new BaseException(e.getMessage(), e);
					}
				}

			}
		}
		if (sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
		}
		if (valueStr.endsWith(",")) {
			valueStr = valueStr.substring(0, valueStr.length() - 1);
		}
		sql += ") values(" + valueStr + ")";
		this.execSqlUpdate(sql, args.toArray());
		return object;
	}

	public void save(Map<String, Object> obj, String tableName) throws BaseException {
		TableMetadata tmd = getTableMetaData(tableName);

		Map sqlMap = tmd.createInsertSql(obj, null, false);
		if (sqlMap == null) {
			throw new BaseException("没有插入数据");
		}

		String sql = (String) sqlMap.get("sql");
		Object[][] params = (Object[][]) sqlMap.get("params");
		this.execSqlUpdateBatch(sql, 1, params[0]);
	}

	public <T> T update(T object) throws BaseException {
		long reqTime = System.currentTimeMillis();
		Class cls = object.getClass();
		Table annotation = (Table) cls.getAnnotation(Table.class);
		String tableName = annotation.name();
		String sql = "update " + tableName + " t set ";
		List args = new ArrayList();
		String idStr = "";
		List idList = new ArrayList();
		Field[] fields = cls.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			boolean hasAnnotation = field.isAnnotationPresent(Column.class);
			if (hasAnnotation) {
				Column column = (Column) field.getAnnotation(Column.class);
				boolean isId = field.isAnnotationPresent(Id.class);
				try {
					if (isId) {
						idStr += " and t." + column.name() + "=? ";
						idList.add(PropertyUtils.getProperty(object, field.getName()));
					} else {
						if (column.isdbcol()) {
							Object obj = PropertyUtils.getProperty(object, field.getName());
							if (obj != null) {
								sql += "t." + column.name() + "=?,";

								if (obj instanceof Date)
									obj = new Timestamp(((Date) obj).getTime());
								args.add(obj);
							}
						}
					}
				} catch (Exception e) {
					throw new BaseException(e.getMessage(), e);
				}
			}
		}
		if (sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
		}

		sql += " where 1=1 ";
		sql += idStr;
		args.addAll(idList);
		this.getJdbcTemplate().update(sql, args.toArray());
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, args));
		return object;
	}

	public <T> T findByPrimaryKey(Class cls, Object... primaryKeyValue) {
		Table annotation = (Table) cls.getAnnotation(Table.class);
		String tableName = annotation.name();
		String sql = "select ";
		String idStr = "";
		Field[] fields = cls.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			boolean hasAnnotation = field.isAnnotationPresent(Column.class);
			if (hasAnnotation) {
				Column column = (Column) field.getAnnotation(Column.class);
				if (column.isdbcol()) {
					sql += column.name() + ",";
				}

				boolean isId = field.isAnnotationPresent(Id.class);
				if (isId) {
					idStr += " and t." + column.name() + "=? ";
				}
			}
		}
		if (sql.endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
		}

		sql += " from " + tableName + " t where 1=1 ";
		sql += idStr;
		List<T> list = this.execSqlQuery(cls, sql, primaryKeyValue);

		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public int execSqlUpdate(String sql) {
		try {
			long reqTime = System.currentTimeMillis();
			int i = this.getJdbcTemplate().update(sql);
			long endTime = System.currentTimeMillis();
			logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
			return i;
		} catch (Exception e) {
			e.printStackTrace();
			throw new BaseException(e.getMessage());
		}
	}

	public int execSqlUpdate(String sql, Object... args) {
		try {
			long reqTime = System.currentTimeMillis();
			int i = this.getJdbcTemplate().update(sql, args);
			long endTime = System.currentTimeMillis();
			logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, args));
			return i;
		} catch (Exception e) {
			e.printStackTrace();
			throw new BaseException(e.getMessage());
		}
	}

	/**
	 * 鎵瑰鏇存柊
	 * 
	 * @param sql
	 *            鏇存柊sql
	 * @param args
	 *            鍙傛暟
	 * @return int[]
	 */
	public int[] execSqlUpdateBatch(String sql, final int size, final Object[]... args) {
		long reqTime = System.currentTimeMillis();
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] obj = args[i];
				for (int j = 0; j < obj.length; j++) {
					if (obj[j] != null && obj[j] instanceof Date) {
						ps.setTimestamp(j + 1, new Timestamp(((Date) obj[j]).getTime()));
					} else {
						ps.setObject(j + 1, obj[j]);
					}
				}

			}

			public int getBatchSize() {
				return size;
			}
		};
		int[] retval = this.getJdbcTemplate().batchUpdate(sql, setter);
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, args));
		return retval;
	}

	public int[] execSqlUpdateBatch(String sql, final int size, final List<Object[]> args) {
		long reqTime = System.currentTimeMillis();
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] obj = args.get(i);
				for (int j = 0; j < obj.length; j++) {
					if (obj[j] != null && obj[j] instanceof Date) {
						ps.setTimestamp(j + 1, new Timestamp(((Date) obj[j]).getTime()));
					} else {
						ps.setObject(j + 1, obj[j]);
					}
				}

			}

			public int getBatchSize() {
				return size;
			}
		};
		int[] retval = this.getJdbcTemplate().batchUpdate(sql, setter);
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + sql);
		return retval;
	}

	@Override
	public PageInfo queryRecordByClassForPageInfo(String sql, Class cls, PageInfo pageinfo) {
		return queryRecordByClassForPageInfo(sql, cls, pageinfo.getPageSize(), pageinfo.getCurPageNO());
	}

	@Override
	public PageInfo queryRecordByClassForPageInfo(String sql, Class cls, PageInfo pageinfo, Object... args) {
		return queryRecordByClassForPageInfo(sql, cls, pageinfo.getPageSize(), pageinfo.getCurPageNO(), args);
	}

	public PageInfo queryRecordByClassForPageInfo(final String sql, Class cls, int pageSize, int curPageNO) {
		long reqTime = System.currentTimeMillis();
		int startRow = pageSize * (curPageNO - 1) + 1;
		int endRow = pageSize * curPageNO;
		String queryString = null;
		queryString = "select * from (" + sql + ") t limit " + (startRow - 1) + "," + pageSize;
		List resultsList = this.execSqlQuery(cls, queryString);
		PageInfo pageInfo = new PageInfo();
		pageInfo.setResultsList(resultsList);
		pageInfo.setCurPageNO(curPageNO);
		pageInfo.setPageSize(pageSize);
		queryString = "select count(*) from (" + sql + ") t ";
		int totalRecord = this.getJdbcTemplate().queryForObject(queryString, Integer.class);
		pageInfo.setTotalRecord(totalRecord);
		int totalPage = (totalRecord % pageSize == 0 ? totalRecord / pageSize : (totalRecord / pageSize) + 1);
		pageInfo.setTotalPage(totalPage);
		pageInfo.setTotalRecord(totalRecord);
		long endTime = System.currentTimeMillis();
		return pageInfo;
	}

	public PageInfo queryRecordByClassForPageInfo(final String sql, Class cls, int pageSize, int curPageNO, Object... args) {
		int startRow = pageSize * (curPageNO - 1) + 1;
		int endRow = pageSize * curPageNO;
		String queryString = null;
		queryString = "select * from (" + sql + ") t limit " + (startRow - 1) + "," + pageSize;
		List resultsList = this.execSqlQuery(cls, queryString, args);
		PageInfo pageInfo = new PageInfo();
		pageInfo.setResultsList(resultsList);
		pageInfo.setCurPageNO(curPageNO);
		pageInfo.setPageSize(pageSize);
		long reqTime = System.currentTimeMillis();
		queryString = "select count(*) from (" + sql + ") t ";
		int totalRecord = this.getJdbcTemplate().queryForObject(queryString, Integer.class, args);
		pageInfo.setTotalRecord(totalRecord);
		int totalPage = (totalRecord % pageSize == 0 ? totalRecord / pageSize : (totalRecord / pageSize) + 1);
		pageInfo.setTotalPage(totalPage);
		pageInfo.setTotalRecord(totalRecord);
		long endTime = System.currentTimeMillis();
		return pageInfo;
	}

	@Override
	public List<Map> queryRecordByMapForPage(String sql, PageInfo pageinfo) {
		return queryRecordByMapForPage(sql, pageinfo.getPageSize(), pageinfo.getCurPageNO());
	}

	public List<Map> queryRecordByMapForPage(final String sql, int pageSize, int curPageNO) {
		int startRow = pageSize * (curPageNO - 1) + 1;
		int endRow = pageSize * curPageNO;
		String queryString = null;
		queryString = "select * from (" + sql + ") t limit " + (startRow - 1) + "," + pageSize;
		List<Map> resultsList = this.execSqlQueryToMap(queryString);
		return resultsList;
	}

	public List<Map> queryRecordByMapForPage(final String sql, int pageSize, int curPageNO, Object... args) {
		int startRow = pageSize * (curPageNO - 1) + 1;
		int endRow = pageSize * curPageNO;
		String queryString = null;
		queryString = "select * from (" + sql + ") t limit " + (startRow - 1) + "," + pageSize;
		List<Map> resultsList = this.execSqlQueryToMap(queryString, args);
		return resultsList;
	}

	public int queryForInt(String sql) {
		long reqTime = System.currentTimeMillis();
		int value = this.getJdbcTemplate().queryForObject(sql, Integer.class);
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
		return value;
	}

	public int queryForInt(String sql, Object... args) {
		long reqTime = System.currentTimeMillis();
		int value = this.getJdbcTemplate().queryForObject(sql, Integer.class, args);
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, args));
		return value;
	}

	public <T> T queryForObj(Class cls, String sql) {
		long reqTime = System.currentTimeMillis();
		T value = null;
		try {
			value = (T) this.getJdbcTemplate().queryForObject(sql, cls);
			long endTime = System.currentTimeMillis();
			logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
		} catch (org.springframework.dao.EmptyResultDataAccessException exception) {
		}
		return value;
	}

	public double queryForDouble(String sql, Object... args) {
		List list = this.execSqlQueryToArrays(sql, args);
		if (list != null && list.size() > 0) {
			Object[] obj = (Object[]) list.get(0);
			return Double.parseDouble(String.valueOf(obj[0]));
		}
		return 0;
	}

	public double queryForDouble(String sql) {
		List list = this.execSqlQueryToArrays(sql);
		if (list != null && list.size() > 0) {
			Object[] obj = (Object[]) list.get(0);
			String doubleStr = String.valueOf(obj[0]);
			if (StringUtils.isEmpty(doubleStr) || "null".equalsIgnoreCase(doubleStr))
				return 0;
			return Double.parseDouble(doubleStr);
		}
		return 0;
	}

	public void updateOracleClob(final String content, String sql, final int index) {
		this.getJdbcTemplate().execute(sql, new AbstractLobCreatingPreparedStatementCallback(this.defaultLobHandler) {

			protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException {
				lobCreator.setClobAsString(ps, index, content); // 璁剧疆璇︾粏鐨凜LOB瀛楁鐨勫唴

			}
		});
	}

	public String getOracleClob(String sql, final int index) {
		final String[] result = new String[1];
		this.getJdbcTemplate().query(sql, new AbstractLobStreamingResultSetExtractor() {

			protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
				result[0] = defaultLobHandler.getClobAsString(rs, index);
			}

		});
		return result[0];
	}

	public <T> List<T> queryForList(String sql, Class<T> c) {
		long reqTime = System.currentTimeMillis();
		List<T> value = this.getJdbcTemplate().queryForList(sql, c);
		long endTime = System.currentTimeMillis();
		logger.info("time: " + String.valueOf(endTime - reqTime) + " ms;  " + this.getLogSql(sql, null));
		return value;
	}

	public <T> List<T> findTableRecords(Class<T> c) {
		Table annotation = (Table) c.getAnnotation(Table.class);
		String tableName = annotation.name();
		Field[] fields = c.getDeclaredFields();
		StringBuilder sqlSB = new StringBuilder();
		sqlSB.append("select");
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			Column column = (Column) field.getAnnotation(Column.class);
			if (column != null && column.isdbcol())
				sqlSB.append(" t.").append(column.name()).append(',');
		}
		sqlSB.deleteCharAt(sqlSB.length() - 1);
		sqlSB.append(" from ").append(tableName).append(" t where 1=1 ");
		List<T> list = this.execSqlQuery(c, sqlSB.toString());
		return list;
	}

	public void callUpdate(final String sql) {
		this.getJdbcTemplate().execute(sql);
	}

	public Map executeCall(final String sql, final Map inparamMap, final Map outparamMap) {
		Map result = new HashMap();
		final int outsize = outparamMap == null ? 0 : outparamMap.size();
		final int insize = inparamMap == null ? 0 : inparamMap.size();
		result = (Map) getJdbcTemplate().execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				CallableStatement stmt = con.prepareCall(sql);
				if (insize > 0) {
					for (Iterator it = inparamMap.entrySet().iterator(); it.hasNext();) {
						Map.Entry m = (Entry) it.next();
						Object value = m.getValue();
						if (value == null) {
							stmt.setNull(Integer.parseInt(m.getKey().toString()), java.sql.Types.VARCHAR);
						} else {
							stmt.setObject(Integer.parseInt(m.getKey().toString()), value);
						}
					}
				}

				if (outsize > 0) {
					for (Iterator it = outparamMap.entrySet().iterator(); it.hasNext();) {
						Map.Entry m = (Entry) it.next();
						stmt.registerOutParameter(Integer.parseInt(m.getKey().toString()), Integer.parseInt(m.getValue().toString()));
					}
				}
				return stmt;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement stmt) throws SQLException, DataAccessException {
				stmt.execute();

				Map result = new HashMap();
				if (outsize > 0) {
					Object[] keys = outparamMap.keySet().toArray();
					Arrays.sort(keys);
					for (int i = 0; i < keys.length; i++) {
						int index = Integer.parseInt(keys[i].toString());
						int type = Integer.parseInt(outparamMap.get(keys[i]).toString());

						if (type == 1) {
							ResultSet rs = ((com.mysql.jdbc.CallableStatement) stmt).getResultSet();
							result.put(Integer.valueOf(index), resultSetToList(rs));
							rs.close();
						} else {
							result.put(Integer.valueOf(index), stmt.getObject(index));
						}
					}
				}
				return result;
			}
		});

		return result;
	}

	private List resultSetToList(ResultSet rs) throws SQLException {
		if (rs == null)
			return Collections.EMPTY_LIST;
		ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
		int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
		List list = new ArrayList();
		Map rowData = new HashMap();
		while (rs.next()) {
			rowData = new HashMap(columnCount);
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}

	/**
	 * Gets the log sql.
	 * 
	 * @param logsession
	 *            the logsession
	 * @param sql
	 *            the sql
	 * @param param
	 *            the param
	 * @return the log sql
	 */
	protected  String getLogSql(final String sql, final Object param) {
		try {
			
			StringBuffer sb = new StringBuffer();
			sb.append(StringUtils.NEW_LINE);
			sb.append(sql);
			sb.append(StringUtils.NEW_LINE);

			if (param != null) {
				// System.out.println("class:" + param.getClass().getName());
				if (param instanceof Object[]) {
					Object[] params = (Object[]) param;
					int pos = -1, cur = 0;

					for (int i = 0; i < params.length; i++) {
						Object obj = params[i];

						if (obj instanceof Collection) {
							List lst = (List) obj;
							for (int k = 0; k < lst.size(); k++) {
								pos = sql.indexOf('?', cur);
								if (pos >= 0) {
									sb.append(sql.substring(cur, pos));
									sb.append(getLogParam(lst.get(k)));

									cur = pos + 1;
								}
							}
						} else {
							pos = sql.indexOf('?', cur);
							if (pos >= 0) {
								sb.append(sql.substring(cur, pos));
								sb.append(getLogParam(obj));
								cur = pos + 1;
							}
						}

					}
					if (cur < sql.length()) {
						sb.append(sql.substring(cur));
					}
				} else if (param instanceof List) {
					List params = (List) param;
					int pos = -1, cur = 0;

					for (int i = 0; i < params.size(); i++) {
						Object obj = params.get(i);

						if (obj instanceof Collection) {
							List lst = (List) obj;
							for (int k = 0; k < lst.size(); k++) {
								pos = sql.indexOf('?', cur);
								if (pos >= 0) {
									sb.append(sql.substring(cur, pos));
									sb.append(getLogParam(lst.get(k)));

									cur = pos + 1;
								}
							}
						} else {
							pos = sql.indexOf('?', cur);
							if (pos >= 0) {
								sb.append(sql.substring(cur, pos));
								sb.append(getLogParam(obj));
								cur = pos + 1;
							}
						}

					}
					if (cur < sql.length()) {
						sb.append(sql.substring(cur));
					}
				} else {
					sb.append(param);
				}
				sb.append(StringUtils.NEW_LINE);
			}

			return sb.toString();
		} catch (Exception ex) {
			ex.printStackTrace();
			try {
				logger.warn("鐢熸垚鏃ュ織SQL璇彞鍑洪敊");
			} catch (Exception logex) {
				logex.printStackTrace();
			}
			return "";
		}
	}

	/**
	 * Gets the log param.
	 * 
	 * @param param
	 *            the param
	 * @return the log param
	 */
	private static String getLogParam(final Object param) {
		if (param == null) {
			return "null";
		}
		if (param instanceof java.sql.Date) {
			return "to_date('" + param.toString().substring(0, 10) + "', 'yyyy-mm-dd')";
		} else if (param instanceof java.sql.Timestamp) {
			return "to_date('" + param.toString().substring(0, 19) + "', 'yyyy-mm-dd hh24:mi:ss')";
		} else {
			return "'" + param.toString() + "'";
		}
	}

	private TableMetadata getTableMetaData(final String tableName) throws BaseException {
		TableMetadata tmd;
		try {
			tmd = MetaDataMapping.getTableMetaData(this.getJdbcTemplate().getDataSource().getConnection(), tableName);
		} catch (SQLException e) {
			throw new BaseException("获取" + tableName + "表的元数据出错");
		}
		if (tmd == null) {
			throw new BaseException(tableName + "表的结构对象不存在");
		}

		return tmd;
	}

}
